package com.joycessh.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.joycessh.bean.EmpAchive;
import com.joycessh.bean.EmpPosition;
import com.joycessh.bean.Employee;
import com.joycessh.dao.ArchiveRegister;
import com.joycessh.util.DBConnectionManager;

public class ArchiveRegisterImpl implements ArchiveRegister {
	
	/**
	 * @author joycessh
	 * 接口ArchiveRegister的实现类
	 */

	DBConnectionManager dcm = null;
	Connection conn = null;
	Statement  stmt = null;
	ResultSet rs = null;
	PreparedStatement prepstmt = null;
	String sqlStr ="";
	Boolean flag = false;
	int effectLines = 0;
	
	@Override
	public boolean insertEmployee(Employee employee) throws SQLException {
		dcm = new DBConnectionManager();
		conn = dcm.getConnection();
		flag = false;
		
		try {
			sqlStr = "insert into employee(Emp_name, sex, email, phone, qq,"
					+ "cell_phone, address, post, country, birth_place, birthday,"
					+ "nation, religion, political_visage, id_number, ssn, age,"
					+ "education, education_years, major, bank,"
					+ "bank_account, specialty, hobby, resume, family, remarks)"
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			prepstmt = conn.prepareStatement(sqlStr);
			prepstmt.setString(1, employee.getEmpName());
			prepstmt.setString(2, employee.getSex());
			prepstmt.setString(3, employee.getEmail());
			prepstmt.setString(4, employee.getPhone());
			prepstmt.setString(5, employee.getQq());
			prepstmt.setString(6, employee.getCellPhone());
			prepstmt.setString(7, employee.getAddress());
			prepstmt.setString(8, employee.getPost());
			prepstmt.setString(9, employee.getCountry());
			prepstmt.setString(10, employee.getBirthPlace());
			prepstmt.setDate(11, employee.getBirthday());
			prepstmt.setString(12, employee.getNation());
			prepstmt.setString(13, employee.getReligion());
			prepstmt.setString(14, employee.getPoliticalVisage());
			prepstmt.setString(15, employee.getIdNumber());
			prepstmt.setString(16, employee.getSsn());
			prepstmt.setString(17, employee.getAge());
			prepstmt.setString(18, employee.getEducation());
			prepstmt.setString(19, employee.getEducationYears());
			prepstmt.setString(20, employee.getMajor());
			//prepstmt.setInt(21, 1);
			prepstmt.setString(21, employee.getBank());
			prepstmt.setString(22, employee.getBankAccount());
			prepstmt.setString(23, employee.getSpecialty());
			prepstmt.setString(24, employee.getHobby());
			prepstmt.setString(25, employee.getResume());
			prepstmt.setString(26, employee.getFamily());
			prepstmt.setString(27, employee.getRemarks());
			effectLines = prepstmt.executeUpdate();
			if (effectLines != 0) {
				flag = true;
			} 	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		  }
		
		//返回employee表的id
		try {
			sqlStr = "select max(id) from employee";
			prepstmt = conn.prepareStatement(sqlStr);
			rs = prepstmt.executeQuery();
			if (rs.next()) {
				employee.setId(rs.getInt(1));
			} 	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		  }
		rs.close();
		prepstmt.close();
		conn.close();
		return flag;
	}

	@Override
	public boolean insertEmpPosition(EmpPosition empPosition) throws SQLException {
		// TODO Auto-generated method stub
		dcm = new DBConnectionManager();
		conn = dcm.getConnection();
		flag = false;
		
		try {
			sqlStr = "insert into emp_position(emp_id, org_i_name, org_ii_name, org_iii_name, position_type_name, position_name)"
					+ "values(?,?,?,?,?,?)";
			prepstmt = conn.prepareStatement(sqlStr);
			prepstmt.setInt(1, empPosition.getEmpId());
			prepstmt.setString(2, empPosition.getOrgIName());
			prepstmt.setString(3, empPosition.getOrgIiName());
			prepstmt.setString(4, empPosition.getOrgIiiName());
			prepstmt.setString(5, empPosition.getPositionTypeName());
			prepstmt.setString(6, empPosition.getPositionName());
			effectLines = prepstmt.executeUpdate();
			if (effectLines != 0) {
				flag = true;
			} 	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		  }
		prepstmt.close();
		conn.close();
		return flag;
	}

	@Override
	public boolean insertEmpAchive(EmpAchive empAchive) throws SQLException {
		// TODO Auto-generated method stub
		dcm = new DBConnectionManager();
		conn = dcm.getConnection();
		flag = false;
		
		try {
			sqlStr = "insert into emp_archive(id, emp_id, booker, book_time, state_type_id) values(?,?,?,?,?)";
			prepstmt = conn.prepareStatement(sqlStr);
			prepstmt.setString(1, empAchive.getId());
			prepstmt.setInt(2, empAchive.getEmpId());
			prepstmt.setString(3, empAchive.getBooker());
			prepstmt.setTimestamp(4, empAchive.getBookTime());
			//prepstmt.setString(5, empAchive.getChecker());
			//prepstmt.setTimestamp(6, empAchive.getCheckTime());
			prepstmt.setInt(5, empAchive.getStateTypeId());
			effectLines = prepstmt.executeUpdate();
			if (effectLines != 0) {
				flag = true;
			} 	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		  }
		return flag;
	}
}
